﻿//-- =============================================
//-- Author:	    Unknown
//-- Create date:   Unknown
//-- Description:	Bank Data Access Layer Class [Get And Rewrite by Saghari, Farid]
//-- E-Mail:    	fredmcs@live.com
//-- Mobile:        0937-241-3499
//-- Last Update:   1390/01/26 23:15    By: Saghari
//-- Important:     سلام دوستان. لطفا کسی این کلاس رو بدون هماهنگی کردن با من تغییر نده
//-- =============================================
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace ChequeManagement.DAL
{
    /// <summary>
    /// this class contain methods for connect to dataBase 
    /// this methods are Insert,Update,FetchAll,Delete,InitialObjectsFromReader,InitialBusinessObjectsFromReader
    /// this class inheritance of BaseDAL
    /// </summary>
    public class DALBank : BaseDAL
    {
        /// <summary>
        /// this constructor called base constructor and set parameter
        /// parameter value is Connectionstring for connect to dataBase
        /// </summary>
        public DALBank()
            : base(ChequeManagement.Properties.Settings.Default.Database1ConnectionString)
        {

        }
        

        /// <summary>
        /// this method is for insert a object of BLL.BLLBank to table
        /// this method check connection if connection is open insert object to table
        /// </summary>
        /// <param name="bankObject" Type="BLL.BLLBank"></param>
        /// <return>this type is void and Do not return</return>
        public void Insert(BLL.BLLBank bankObject)
        {
            bool controlConnection = !(this.ConnectionState == System.Data.ConnectionState.Open);
            this.Command.CommandText = "uspBankInsert";
            this.Command.CommandType = CommandType.StoredProcedure;

            try
               {
                this.Command.Parameters.Clear();

                SqlParameter bankID = new SqlParameter();
                bankID.ParameterName = "@bankID";
                bankID.SqlDbType = SqlDbType.Int;
                bankID.Direction = ParameterDirection.Output;
                bankID.IsNullable = false;
                bankID.Value = null;
                this.Command.Parameters.Add(bankID);

                SqlParameter bankName = new SqlParameter();
                bankName.ParameterName = "@bankName";
                bankName.SqlDbType = SqlDbType.NVarChar;
                bankName.Direction = ParameterDirection.Input;
                bankName.IsNullable = false;
                bankName.Value = bankObject.BankName;
                this.Command.Parameters.Add(bankName);

                if (controlConnection)
                    this.BeginTransaction();

                this.Command.ExecuteNonQuery();

                bankObject.BankID = Convert.ToInt32(this.Command.Parameters["@bankID"].Value);

                if (controlConnection)
                    this.CommitTransaction();
            }
            catch (Exception ex)
            {
                if (controlConnection)
                    this.RollBackTransaction();
                throw (ex);
            }
        }

        /// <summary>
        /// this method is for Update a object of BLL.BLLBank to table by BankID
        /// this method check connection if connection is open Update object to table
        /// </summary>
        /// <param name="bankObject" Type="BLL.BLLBank"></param>
        /// <param name="targetBankID" Type="int"></param>
        /// <return>this type is void and Do not return</return>
        public void Update(BLL.BLLBank bankObject, int targetBankID)
        {
            bool controlConnection = !(this.ConnectionState == System.Data.ConnectionState.Open);
            this.Command.CommandText = "uspBankUpdate";
            this.Command.CommandType = CommandType.StoredProcedure;

            try
            {
                this.Command.Parameters.Clear();

                SqlParameter pkBankID = new SqlParameter();
                pkBankID.ParameterName = "@pkBankID";
                pkBankID.SqlDbType = SqlDbType.Int;
                pkBankID.Direction = ParameterDirection.Input;
                pkBankID.IsNullable = false;

                pkBankID.Value = targetBankID;
                // روشی که پیشنهاد دادین خوبه ولی نه همیشه
                // اجازه بدین من در کلاس های بعدی میگم چرا؟
                //pkBankID.Value = bankObject.BankID;

                this.Command.Parameters.Add(pkBankID);

                SqlParameter bankName = new SqlParameter();
                bankName.ParameterName = "@bankName";
                bankName.SqlDbType = SqlDbType.NVarChar;
                bankName.Direction = ParameterDirection.Input;
                bankName.IsNullable = false;
                bankName.Value = bankObject.BankName;
                this.Command.Parameters.Add(bankName);

                if (controlConnection)
                    this.BeginTransaction();

                this.Command.ExecuteNonQuery();

                if (controlConnection)
                    this.CommitTransaction();
            }
            catch (Exception ex)
            {
                if (controlConnection)
                    this.RollBackTransaction();
                throw (ex);
            }
        }

        /// <summary>
        /// this method is for FetchAll all rows from table
        /// this method check connection if connection is open FetchAll all rows from table
        /// </summary>
        /// <param name="bankDT" Type="DataTable"></param>
        /// <return>this type is void but result return to DataTable </return>
        public void FetchAll(DataTable bankDT)
        {
            bool controlConnection = !(this.ConnectionState == System.Data.ConnectionState.Open);
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(this.Command);
            this.Command.CommandText = "uspBankFetchAll";
            this.Command.CommandType = CommandType.StoredProcedure;

            try
            {
                this.Command.Parameters.Clear();

                if (controlConnection)
                    this.BeginTransaction();

                sqlDataAdapter.Fill(bankDT);

                if (controlConnection)
                    this.CommitTransaction();
            }
            catch (Exception ex)
            {
                if (controlConnection)
                    this.RollBackTransaction();
                throw (ex);
            }
        }

        /// <summary>
        /// this method is for FetchAll all rows from table
        /// this method check connection if connection is open FetchAll all rows from table
        /// </summary>
        /// <return>result return to a list<> of ChequeManagement.BLL.BLLBank </return>
        public List<ChequeManagement.BLL.BLLBank> FetchAll()
        {
            bool controlConnection = !(this.ConnectionState == System.Data.ConnectionState.Open);
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(this.Command);
            this.Command.CommandText = "uspBankFetchAll";
            this.Command.CommandType = CommandType.StoredProcedure;
            IDataReader dataReader=null;
            try
            {
                this.Command.Parameters.Clear();

                if (controlConnection)
                    this.BeginTransaction();

                
                List<BLL.BLLBank> result = new List<BLL.BLLBank>();

                using (dataReader = this.Command.ExecuteReader())
                {
                    result = InitialObjectsFromReader(dataReader);
                }
                
                if (controlConnection)
                    this.CommitTransaction();

                return (result);

            }

            catch (Exception ex)
            {
                if (controlConnection)
                    this.RollBackTransaction();
                throw (ex);
               
            }
             
            
        }

        /// <summary>
        /// this method is for FetchAll all rows from table by field name that field name value is this vlaue
        /// this method check connection if connection is open FetchAll all rows from table
        /// </summary>
        /// <param name="fieldName" Type="string"></param>
        /// <param name="value" Type="object"></param>
        /// <returns>result return to a list<> of BLL.BLLBank</returns>
        public List<BLL.BLLBank> FetchByField(string fieldName, object value)
        {
            bool controlConnection = !(this.ConnectionState == System.Data.ConnectionState.Open);
            this.Command.CommandText = "uspBankFetchCondition";
            this.Command.CommandType = CommandType.StoredProcedure;

            try
            {
                this.Command.Parameters.Clear();

                if (controlConnection)
                    this.BeginTransaction();

                this.Command.Parameters.Add(new SqlParameter("@condition", "[Bank].[" + fieldName + "] = N'" + value.ToString() + "'"));

                
                List<BLL.BLLBank> result = new List<BLL.BLLBank>();
                using (IDataReader dataReader = this.Command.ExecuteReader())
                {
                    result = InitialObjectsFromReader(dataReader);
                }
                if (controlConnection)
                    this.CommitTransaction();

                return (result);
            }
            catch (Exception ex)
            {
                if (controlConnection)
                    this.RollBackTransaction();
                throw (ex);
            }
        }

        /// <summary>
        /// this method is for FetchAll all rows from table by field name that field name value is this vlaue
        /// this method check connection if connection is open FetchAll all rows from table
        /// </summary>
        /// <param name="fieldName" Type="string"></param>
        /// <param name="value" Type="object"></param>
        /// <param name="bankDT" Type="DataTable"></param>
        /// <return>this type is void but return value to DataTable </return>
        public void FetchByField(string fieldName, object value, DataTable bankDT)
        {
            bool controlConnection = !(this.ConnectionState == System.Data.ConnectionState.Open);
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(this.Command);
            this.Command.CommandText = "uspBankFetchCondition";
            this.Command.CommandType = CommandType.StoredProcedure;

            try
            {
                this.Command.Parameters.Clear();

                if (controlConnection)
                    this.BeginTransaction();

                this.Command.Parameters.Add(new SqlParameter("@condition", "[Bank].[" + fieldName + "] = N'" + value.ToString() + "'"));

                sqlDataAdapter.Fill(bankDT);

                if (controlConnection)
                    this.CommitTransaction();
            }
            catch (Exception ex)
            {
                if (controlConnection)
                    this.RollBackTransaction();
                throw (ex);
            }
        }

        /// <summary>
        /// this method is for FetchAll all rows from table by condition 
        /// this method check connection if connection is open FetchAll all rows from table
        /// </summary>
        /// <param name="condition" Type="string"></param>
        /// <param name="bankDT" Type="DataTable"></param>
        /// <return>this type is void but return value to DataTable </return>
        public void FetchAllByCondition(string condition, DataTable bankDT)
        {
            bool controlConnection = !(this.ConnectionState == System.Data.ConnectionState.Open);
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(this.Command);
            this.Command.CommandText = "uspBankFetchCondition";
            this.Command.CommandType = CommandType.StoredProcedure;

            try
            {
                this.Command.Parameters.Clear();

                if (controlConnection)
                    this.BeginTransaction();

                this.Command.Parameters.Add(new SqlParameter("@condition", condition));

                sqlDataAdapter.Fill(bankDT);

                if (controlConnection)
                    this.CommitTransaction();
            }
            catch (Exception ex)
            {
                if (controlConnection)
                    this.RollBackTransaction();
                throw (ex);
            }
        }

        /// <summary>
        /// this method is for FetchAll all rows from table by BankID 
        /// this method check connection if connection is open FetchAll all rows from table
        /// </summary>
        /// <param name="bankID" Type="int"></param>
        /// <returns>return a object that type is BLL.BLLBank</returns>
        public BLL.BLLBank FetchByKey(int bankID)
        {
            bool controlConnection = !(this.ConnectionState == System.Data.ConnectionState.Open);
            this.Command.CommandText = "uspBankFetchByPK";
            this.Command.CommandType = CommandType.StoredProcedure;

            try
            {
                this.Command.Parameters.Clear();

                SqlParameter pkbankID = new SqlParameter();
                pkbankID.ParameterName = "@bankID";
                pkbankID.SqlDbType = SqlDbType.Int;
                pkbankID.Direction = ParameterDirection.Input;
                pkbankID.IsNullable = false;
                pkbankID.Value = bankID;
                this.Command.Parameters.Add(pkbankID);

                if (controlConnection)
                    this.BeginTransaction();

                
                BLL.BLLBank bankObject = new BLL.BLLBank();

                using (IDataReader dataReader = this.Command.ExecuteReader())
                {
                    if (dataReader.Read())
                        InitialBusinessObjectsFromReader(bankObject, dataReader);
                    else
                        bankObject = null;
                }

                if (controlConnection)
                    this.CommitTransaction();

                return (bankObject);
            }
            catch (Exception ex)
            {
                if (controlConnection)
                    this.RollBackTransaction();
                throw (ex);
            }
        }
        
        /// <summary>
        /// this method is for Delete a row from table
        /// this method check connection if connection is open Delete a row
        /// </summary>
        /// <param name="bankID" Type="int"></param>
        /// <return>this type is void and Do not return</return>
        public void Delete(int bankID)
        {
            bool controlConnection = !(this.ConnectionState == System.Data.ConnectionState.Open);
            this.Command.CommandText = "uspBankDeleteByPK";
            this.Command.CommandType = CommandType.StoredProcedure;

            try
            {
                this.Command.Parameters.Clear();

                SqlParameter pkbankID = new SqlParameter();
                pkbankID.ParameterName = "@bankID";
                pkbankID.SqlDbType = SqlDbType.Int;
                pkbankID.Direction = ParameterDirection.Input;
                pkbankID.IsNullable = false;
                pkbankID.Value = bankID;
                this.Command.Parameters.Add(pkbankID);

                if (controlConnection)
                    this.BeginTransaction();

                this.Command.ExecuteNonQuery();

                if (controlConnection)
                    this.CommitTransaction();
            }
            catch (Exception ex)
            {
                if (controlConnection)
                    this.RollBackTransaction();
                throw (ex);
            }
        }

        /// <summary>
        /// this method is for Delete a row from table by field name that field name value is this value
        /// this method check connection if connection is open Delete a row
        /// </summary>
        /// <param name="fieldName" Type="string"></param>
        /// <param name="value" Type="object"></param>
        /// <return>this type is void and Do not return</return>
        public void Delete(string fieldName, object value)
        {
            bool controlConnection = !(this.ConnectionState == System.Data.ConnectionState.Open);
            this.Command.CommandText = "uspBankDeleteByField";
            this.Command.CommandType = CommandType.StoredProcedure;

            try
            {
                this.Command.Parameters.Clear();

                this.Command.Parameters.Add(new SqlParameter("@fieldName", fieldName));
                this.Command.Parameters.Add(new SqlParameter("@value", value));

                if (controlConnection)
                    this.BeginTransaction();

                this.Command.ExecuteNonQuery();

                if (controlConnection)
                    this.CommitTransaction();
            }
            catch (Exception ex)
            {
                if (controlConnection)
                    this.RollBackTransaction();
                throw (ex);
            }
        }

        /// <summary>
        /// this method give columns value by number column
        /// </summary>
        /// <param name="dataReader" Type="IDataReader"></param>
        /// <returns>return a list<> that value is table columns value</returns>
        private List<BLL.BLLBank> InitialObjectsFromReader(IDataReader dataReader)
        {
            List<BLL.BLLBank> banksList = new List<BLL.BLLBank>();

            int bankIDColumn = dataReader.GetOrdinal(BLL.BLLBank.BankField.BankID.ToString());
            int bankNameColumn = dataReader.GetOrdinal(BLL.BLLBank.BankField.BankName.ToString());

            while (dataReader.Read())
            {
                BLL.BLLBank bankObject = new BLL.BLLBank();

                bankObject.BankID = dataReader.GetInt32(bankIDColumn);
                bankObject.BankName = dataReader.GetString(bankNameColumn);
                banksList.Add(bankObject);
            }
            return (banksList);
        }

        /// <summary>
        ///  this method give columns value by number column
        /// </summary>
        /// <param name="bankObject" Type="BLL.BLLBank"></param>
        /// <param name="dataReader" Type="IDataReader"></param>
        /// <returns>this method is void but return a object of BLL.BLLBank that value is table columns value</returns>
        private void InitialBusinessObjectsFromReader(BLL.BLLBank bankObject, IDataReader dataReader)
        {
            bankObject.BankID = dataReader.GetInt32(dataReader.GetOrdinal(BLL.BLLBank.BankField.BankID.ToString()));
            bankObject.BankName = dataReader.GetString(dataReader.GetOrdinal(BLL.BLLBank.BankField.BankName.ToString()));
        }
    }
}
